﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AfvandingService.DOAs
{
    internal class CentralDOA : ICentralDOA
    {
        private string CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["Afvandingsprojekt"].ConnectionString;

        public void OpretPumpestation(Pumpestation ps)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_OpretPumpeStation", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", ps.Pumpestationsnr);
                cmd.Parameters.AddWithValue("@Netværksadresse", ps.Netværksadresse);
                cmd.Parameters.AddWithValue("@IPadresse", ps.IP.ToString());
                cmd.Parameters.AddWithValue("@Portnummer", ps.Port);
                cmd.Parameters.AddWithValue("@Vandstandsrisiko", ps.Vandstandsrisiko);
                cmd.Parameters.AddWithValue("@Vandstand", ps.Vandstand);

                cmd.ExecuteNonQuery();
            }
        }

        public void OpdatérPumpestation(int psnr, string netværksadd, string ip, int port, string vandstandsrisiko, double vandstand)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_OpdatérPumpeStation", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", psnr);
                cmd.Parameters.AddWithValue("@Netværksadresse", netværksadd);
                cmd.Parameters.AddWithValue("@IPadresse", ip);
                cmd.Parameters.AddWithValue("@Portnummer", port);
                cmd.Parameters.AddWithValue("@Vandstandsrisiko", vandstandsrisiko);
                cmd.Parameters.AddWithValue("@Vandstand", vandstand);

                cmd.ExecuteNonQuery();
            }
        }

        public void SletPumpestation(int psnr)
        {
            //foreach (var Pumpestation in PumpestationsListe)
            //{
            //    if (Pumpestation.Pumpestationsnr == pumpestationsnr)
            //    {
            //        foreach (var Pumpe in Pumpestation.Pumpeliste)
            //        {
            //            //SletPumpeSQL(pumpestationsnr, Pumpe.Pumpenr);
            //        }
            //        break;
            //    }
            //}

            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_SletPumpeStation", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", psnr);
                cmd.ExecuteNonQuery();
            }
        }

        public double EnPSVandstand(int psnr)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_PumpestationVandstand", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", psnr);
                cmd.ExecuteNonQuery();

                SqlDataReader datareader = cmd.ExecuteReader();

                while (datareader.Read() == true)
                {
                    double vandstand = (double)datareader["Vandstand"];
                    return vandstand;
                }
            }
            throw new Exception("Vandstanden kunne ikke hentes");
        }

        public List<double> AllePSVandstande()
        {
            List<double> vandstandsListe = new List<double>();
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_PumpestationerVandstand", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.ExecuteNonQuery();

                SqlDataReader datareader = cmd.ExecuteReader();
                while (datareader.Read() == true)
                {
                    double vandstand = (double)datareader["Vandstand"];
                    vandstandsListe.Add(vandstand);
                }
            }
            return vandstandsListe;
        }

        public List<Pumpestation> HentPumpestationerOgPumper() 
        {
            List<Pumpestation> TEMPpumpestationer = new List<Pumpestation>();

            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_HentPumpeStationer", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Pumpestation TEMPstation = new Pumpestation();
                    
                    TEMPstation.Pumpestationsnr = (int)reader["PumpestationsNr"];
                    TEMPstation.Netværksadresse = reader["Netværksadresse"].ToString();
                    TEMPstation.IP = reader["IPadresse"].ToString();
                    TEMPstation.Port = (int)reader["Portnummer"];
                    TEMPstation.Vandstandsrisiko = reader["Vandstandsrisiko"].ToString();
                    TEMPstation.Vandstand = double.Parse(reader["Vandstand"].ToString());

                    TEMPstation.Pumpeliste = HentPumpestationsPumper(TEMPstation.Pumpestationsnr, conn);
                    TEMPpumpestationer.Add(TEMPstation);
                }
            }
            return TEMPpumpestationer;
        }

        public List<Pumpe> HentPumpestationsPumper(int psnr, SqlConnection conn) 
        {
            List<Pumpe> tempListe = new List<Pumpe>();
            SqlCommand cmd = new SqlCommand("SP_HentPumpe", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Pumpestationsnr", psnr);
            SqlDataReader reader = cmd.ExecuteReader();            

            while (reader.Read())
            {
                Pumpe temp = new Pumpe();

                temp.PumpestationsNr = psnr;
                temp.Pumpenr = (int) reader["Pumpenr"];
                temp.Pumpekapacitet = (double)reader["Pumpekapacitet"];
                temp.Status = (bool)reader["PumpeStatus"];

                tempListe.Add(temp);
            }
            return tempListe;
        }

        // --- Log SQL --- //
        public void OpretLogEntry(DateTime tidspunkt, int pumpestationsnr, double vandstand)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_NyVandstand", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Tidspunkt", tidspunkt);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.Parameters.AddWithValue("@Vandstand", vandstand);
                cmd.ExecuteNonQuery();
            }
        }
        
        public Dictionary<double, DateTime> HentLog(int psnr) 
        {
            Dictionary<double, DateTime> vandstandsLog = new Dictionary<double, DateTime>();
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_HentVandstandslog", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PumpestationsNr", psnr);
                conn.Open();

                cmd.ExecuteNonQuery();

                SqlDataReader datareader = cmd.ExecuteReader();
                while (datareader.Read() == true)
                {
                    DateTime tidspunkt = (DateTime)datareader["Tidspunkt"];
                    double vandstand = (double)datareader["Vandstand"];
                    vandstandsLog.Add(vandstand, tidspunkt);
                }
            }
            return vandstandsLog;
        }       
    }
}
